﻿using System;
using System.IO;
using System.IO.Packaging;
using System.Text;
using System.Xml;
using System.ComponentModel;
using System.ComponentModel.Design;
using System.Collections;
using System.Collections.Generic;
using System.Activities;
using System.Activities.Expressions;
using System.Activities.Presentation.PropertyEditing;
using System.Activities.Statements;

namespace Vision.Core.ActivityComponents.Office
{
    /// <summary>
    /// 
    /// </summary>
    public class CreateExcelDocument : CodeActivity
    {
        /// <summary>
        /// 
        /// </summary>
        [Browsable(true)]
        [DefaultValue(null)]        
        public InArgument<string> FilePath { get; set; }

        /// <summary>
        /// 
        /// </summary>
        [Browsable(true)]
        [DefaultValue(null)]        
        public InArgument<string> SheetName { get; set; }
                
        /// <summary>
        /// 
        /// </summary>
        [Browsable(true)]
        [DefaultValue(null)]        
        public InArgument<Dictionary<string, object>> Cells { get; set; }
                        
        /// <summary>
        /// 
        /// </summary>
        /// <param name="context"></param>
        protected override void Execute(CodeActivityContext context)
        {
            ExcelUtil.CreateEmptyXlsx(context.GetValue(this.FilePath), context.GetValue(this.SheetName));
            foreach (string s in context.GetValue(this.Cells).Keys){
                ExcelUtil.WriteCell(context.GetValue(this.FilePath), context.GetValue(this.SheetName), s, context.GetValue(this.Cells)[s].ToString());                
            }
        }
    }

    /// <summary>
    /// 
    /// </summary>
    internal static class ExcelUtil
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="sheetName"></param>
        public static void CreateEmptyXlsx(string fileName, string sheetName)
        {
            const string spreadsheetML = @"http://schemas.openxmlformats.org/spreadsheetml/2006/5/main";
            const string relationSchema = @"http://schemas.openxmlformats.org/officeDocument/2006/relationships";
            const string workbookContentType = @"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml";
            const string worksheetContentType = @"application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml";
            const string stylesheetContentType = @"application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml";
            const string stringsContentType = @"application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml";

            // create the workbook "start part"
            XmlDocument xmlStartPart = new XmlDocument();
            // create the workbook tag
            XmlElement tagWorkbook = xmlStartPart.CreateElement("workbook", spreadsheetML);
            // Add the relationships namespace
            XmlAttribute nsAttribute = xmlStartPart.CreateAttribute("xmlns", "r", @"http://www.w3.org/2000/xmlns/");
            nsAttribute.Value = relationSchema;
            tagWorkbook.Attributes.Append(nsAttribute);
            xmlStartPart.AppendChild(tagWorkbook);
            // create the sheets tag
            XmlElement tagSheets = xmlStartPart.CreateElement("sheets", spreadsheetML);
            tagWorkbook.AppendChild(tagSheets);
            // create the sheet tag (must have at least one worksheet), and set name/SheetId attributes
            XmlElement tagSheet = xmlStartPart.CreateElement("sheet", spreadsheetML);
            tagSheet.SetAttribute("name", sheetName);
            tagSheet.SetAttribute("sheetId", "1");
            // set the r:id attribute
            tagSheet.SetAttribute("id", relationSchema, "rId1");
            // insert the sheet tag with all attributes set as above
            tagSheets.AppendChild(tagSheet);

            // create the worksheet
            XmlDocument xmlWorksheet = new XmlDocument();
            XmlElement tagWorksheet = xmlWorksheet.CreateElement("worksheet", spreadsheetML);
            tagWorksheet.SetAttribute("xmlns:r", relationSchema);
            xmlWorksheet.AppendChild(tagWorksheet);
            // create the sheetViews tag
            XmlElement tagSheetViews = xmlWorksheet.CreateElement("sheetViews", spreadsheetML);
            tagWorksheet.AppendChild(tagSheetViews);
            // create the sheet View tag
            XmlElement tagSheetView = xmlWorksheet.CreateElement("sheetView", spreadsheetML);
            tagSheetView.SetAttribute("workbookViewId", "0");
            tagSheetViews.AppendChild(tagSheetView);
            // create the empty sheetData tag (must be present, but can be empty)
            XmlElement tagSheetData = xmlWorksheet.CreateElement("sheetData", spreadsheetML);
            tagWorksheet.AppendChild(tagSheetData);

            // create the style sheet
            XmlDocument xmlStylesheet = new XmlDocument();
            XmlElement tagStylesheet = xmlStylesheet.CreateElement("styleSheet", spreadsheetML);
            xmlStylesheet.AppendChild(tagStylesheet);
            // create the fonts tag
            XmlElement tagFonts = xmlStylesheet.CreateElement("fonts", spreadsheetML);
            tagFonts.SetAttribute("count", "1");
            tagStylesheet.AppendChild(tagFonts);
            // create the font tag
            XmlElement tagFont = xmlStylesheet.CreateElement("font", spreadsheetML);
            tagFonts.AppendChild(tagFont);
            // create the sz tag
            XmlElement tagSz = xmlStylesheet.CreateElement("sz", spreadsheetML);
            tagSz.SetAttribute("val", "11");
            tagFont.AppendChild(tagSz);
            // create the name tag
            XmlElement tagName = xmlStylesheet.CreateElement("name", spreadsheetML);
            tagName.SetAttribute("val", "Calibri");
            tagFont.AppendChild(tagName);
            // create the cellStyleXfs tag
            XmlElement tagCellStyleXfs = xmlStylesheet.CreateElement("cellStyleXfs", spreadsheetML);
            tagCellStyleXfs.SetAttribute("count", "1");
            tagStylesheet.AppendChild(tagCellStyleXfs);
            // create the xf tag
            XmlElement tagXf = xmlStylesheet.CreateElement("xf", spreadsheetML);
            tagXf.SetAttribute("numFmtId", "0");
            tagXf.SetAttribute("fontId", "0");
            tagCellStyleXfs.AppendChild(tagXf);
            // create the cellXfs tag
            XmlElement tagCellXfs = xmlStylesheet.CreateElement("cellXfs", spreadsheetML);
            tagCellXfs.SetAttribute("count", "1");
            tagStylesheet.AppendChild(tagCellXfs);
            // create the xf tag
            XmlElement tagXf2 = xmlStylesheet.CreateElement("xf", spreadsheetML);
            tagXf2.SetAttribute("numFmtId", "0");
            tagXf2.SetAttribute("fontId", "0");
            tagXf2.SetAttribute("xfId", "0");
            tagCellXfs.AppendChild(tagXf2);

            // create the shared-strings table (with no entries in it)
            XmlDocument xmlStrings = new XmlDocument();
            XmlElement tagSst = xmlStrings.CreateElement("sst", spreadsheetML);
            tagSst.SetAttribute("count", "0");
            tagSst.SetAttribute("uniqueCount", "0");
            xmlStrings.AppendChild(tagSst);

            // create the package (i.e., the document container)
            Package pkgOutputDoc = null;
            pkgOutputDoc = Package.Open(fileName, FileMode.Create, FileAccess.ReadWrite);

            // save a temporary part to create the default application/xml content type
            Uri uriDefaultContentType = new Uri("/default.xml", UriKind.Relative);
            PackagePart partTemp = pkgOutputDoc.CreatePart(uriDefaultContentType, "application/xml");

            // save the main document part (workbook.xml)
            Uri uriStartPart = new Uri("/xl/workbook.xml", UriKind.Relative);
            PackagePart partWorkbookXML = pkgOutputDoc.CreatePart(uriStartPart, workbookContentType);
            StreamWriter streamStartPart = new StreamWriter(partWorkbookXML.GetStream(FileMode.Create, FileAccess.Write));
            xmlStartPart.Save(streamStartPart);
            streamStartPart.Close();
            pkgOutputDoc.Flush();

            // save the worksheet (sheet1.xml)
            Uri uriWorksheet = new Uri("/xl/worksheets/sheet1.xml", UriKind.Relative);
            PackagePart partWorksheetXML = pkgOutputDoc.CreatePart(uriWorksheet, worksheetContentType);
            StreamWriter streamWorksheet = new StreamWriter(partWorksheetXML.GetStream(FileMode.Create, FileAccess.Write));
            xmlWorksheet.Save(streamWorksheet);
            streamWorksheet.Close();
            pkgOutputDoc.Flush();

            // save the stylesheet (styles.xml)
            Uri uriStylesheet = new Uri("/xl/styles.xml", UriKind.Relative);
            PackagePart partStylesheetXML = pkgOutputDoc.CreatePart(uriStylesheet, stylesheetContentType);
            StreamWriter streamStylesheet = new StreamWriter(partStylesheetXML.GetStream(FileMode.Create, FileAccess.Write));
            xmlStylesheet.Save(streamStylesheet);
            streamStylesheet.Close();
            pkgOutputDoc.Flush();

            // save the shared-strings table (sharedStrings.xml)
            Uri uriStrings = new Uri("/xl/sharedStrings.xml", UriKind.Relative);
            PackagePart partStrings = pkgOutputDoc.CreatePart(uriStrings, stringsContentType);
            StreamWriter streamStrings = new StreamWriter(partStrings.GetStream(FileMode.Create, FileAccess.Write));
            xmlStrings.Save(streamStrings);
            streamStrings.Close();
            pkgOutputDoc.Flush();

            // create the relationship parts
            pkgOutputDoc.CreateRelationship(uriStartPart, TargetMode.Internal, relationSchema + "/officeDocument", "rId1");
            partWorkbookXML.CreateRelationship(uriWorksheet, TargetMode.Internal, relationSchema + "/worksheet", "rId1");
            partWorkbookXML.CreateRelationship(uriStylesheet, TargetMode.Internal, relationSchema + "/styles", "rId2");
            partWorkbookXML.CreateRelationship(uriStrings, TargetMode.Internal, relationSchema + "/sharedStrings", "rId3");

            // remove the temporary part that created the default xml content type
            pkgOutputDoc.DeletePart(uriDefaultContentType);

            // close the document
            pkgOutputDoc.Flush();
            pkgOutputDoc.Close();
        }

        /// <summary>
        /// Note that cellvalue is passed as a string, but will be converted to int32 if appropriate.
        /// </summary>
        /// <param name="filename"></param>
        /// <param name="worksheet"></param>
        /// <param name="celladdress"></param>
        /// <param name="cellvalue"></param>
        /// <returns></returns>
        public static bool WriteCell(string filename, string worksheet, string celladdress, string cellvalue)        
        {
            Console.WriteLine("writing cell " + celladdress + ", value = " + cellvalue);
            if (IsInteger(cellvalue))
            {
                XLInsertNumberIntoCell(filename, worksheet, celladdress, Int32.Parse(cellvalue));
            }
            else
            {
                XLInsertStringIntoCell(filename, worksheet, celladdress, cellvalue);
            }
            return true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="sheetName"></param>
        /// <param name="addressName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static bool XLInsertNumberIntoCell(string fileName, string sheetName, string addressName, int value)
        {
            //  Given a file, a sheet, and a cell, insert a specified value.
            //  For example: InsertNumberIntoCell("C:\Test.xlsx", "Sheet3", "C3", 14)

            //  If the cell element (<c>) already exists, simply replace the <c> element with the new number value, maintaining formatting.
            //  If the row exists, but not the cell, insert the new <c> element.
            //  If the row doesn't exist, create the <row> element, and then insert the appropriate <c> element.
            //  In each case, you insert a fully created <c> element -- the question is how much 
            //  "stuff" you have to create around the new <c> element.

            //  Set the value of the specified cell.
            const string documentRelationshipType = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument";
            const string worksheetSchema = "http://schemas.openxmlformats.org/spreadsheetml/2006/5/main";

            //  Retrieve the stream containing the requested
            //  worksheet's info:
            PackagePart documentPart = null;
            Uri documentUri = null;
            bool returnValue = false;

            using (Package xlPackage = Package.Open(fileName, FileMode.Open, FileAccess.ReadWrite))
            {
                //  Get the main document part (workbook.xml).
                foreach (System.IO.Packaging.PackageRelationship relationship in xlPackage.GetRelationshipsByType(documentRelationshipType))
                {
                    //  There should only be one document part in the package. 
                    documentUri = PackUriHelper.ResolvePartUri(new Uri("/", UriKind.Relative), relationship.TargetUri);
                    documentPart = xlPackage.GetPart(documentUri);
                    //  There should only be one instance, but get out no matter what.
                    break;
                }

                if (documentPart != null)
                {
                    // Load the contents of the workbook.
                    XmlDocument doc = new XmlDocument();
                    doc.Load(documentPart.GetStream());

                    // Create a NamespaceManager to handle the default namespace, 
                    // and create a prefix for the default namespace:
                    XmlNamespaceManager nsManager = new XmlNamespaceManager(doc.NameTable);
                    nsManager.AddNamespace("d", doc.DocumentElement.NamespaceURI);

                    string searchString = string.Format("//d:sheet[@name='{0}']", sheetName);
                    XmlNode sheetNode = doc.SelectSingleNode(searchString, nsManager);
                    if (sheetNode != null)
                    {
                        //  Get the relId attribute:
                        XmlAttribute relationAttribute = sheetNode.Attributes["r:id"];
                        if (relationAttribute != null)
                        {
                            string relId = relationAttribute.Value;

                            //  First, get the relation between the document and the sheet.
                            PackageRelationship sheetRelation = documentPart.GetRelationship(relId);
                            Uri sheetUri = PackUriHelper.ResolvePartUri(documentUri, sheetRelation.TargetUri);
                            PackagePart sheetPart = xlPackage.GetPart(sheetUri);

                            //  Load the contents of the sheet into an XML document.
                            XmlDocument xDoc = new XmlDocument();
                            xDoc.Load(sheetPart.GetStream());

                            //  Use regular expressions to get the row number.
                            //  If the parameter wasn't well formed, this code
                            //  will fail:
                            System.Text.RegularExpressions.Regex r = new System.Text.RegularExpressions.Regex(@"^(?<col>\D+)(?<row>\d+)");
                            string rowNumber = r.Match(addressName).Result("${row}");

                            //  Search for the existing cell:
                            XmlNode cellnode = xDoc.SelectSingleNode(string.Format("//d:sheetData/d:row/d:c[@r='{0}']", addressName), nsManager);
                            if (cellnode == null)
                            {
                                //  Didn't find the cell. Look for the row. You're going to need to create
                                //  the cell whether you find the row or not, so create that XML element now:
                                XmlElement cellElement = xDoc.CreateElement("c", worksheetSchema);
                                cellElement.Attributes.Append(xDoc.CreateAttribute("r"));
                                cellElement.Attributes["r"].Value = addressName;

                                XmlElement valueElement = xDoc.CreateElement("v", worksheetSchema);
                                valueElement.InnerText = value.ToString();
                                cellElement.AppendChild(valueElement);

                                //  Default style is "0"
                                cellElement.Attributes.Append(xDoc.CreateAttribute("s"));
                                cellElement.Attributes["s"].Value = "0";

                                XmlNode rowNode = xDoc.SelectSingleNode(string.Format("//d:sheetData/d:row[@r='{0}']", rowNumber), nsManager);
                                if (rowNode == null)
                                {
                                    //  Didn't find the row, either. Just add a new row element:
                                    XmlNode sheetDataNode = xDoc.SelectSingleNode("//d:sheetData", nsManager);
                                    if (sheetDataNode != null)
                                    {
                                        XmlElement rowElement = xDoc.CreateElement("row", worksheetSchema);
                                        rowElement.Attributes.Append(xDoc.CreateAttribute("r"));
                                        rowElement.Attributes["r"].Value = rowNumber;
                                        rowElement.AppendChild(cellElement);
                                        sheetDataNode.AppendChild(rowElement);
                                        returnValue = true;
                                    }
                                }
                                else
                                {
                                    //  The row exists, but the cell does not. Add the cell information now.
                                    //  Get the style information from the parent row, if it exists.
                                    XmlAttribute styleAttr = ((XmlAttribute)(rowNode.Attributes.GetNamedItem("s")));
                                    if (styleAttr != null)
                                    {
                                        //  You know cellElement has an "s" attribute -- you
                                        //  added it yourself.
                                        cellElement.Attributes["s"].Value = styleAttr.Value;
                                    }

                                    // You must insert the new cell at the correct location.
                                    // Loop through the children, looking for the first cell that is
                                    // beyond the cell you're trying to insert. Insert before that cell.
                                    XmlNode biggerNode = null;
                                    XmlNodeList cellNodes = rowNode.SelectNodes("./d:c", nsManager);
                                    if (cellNodes != null)
                                    {
                                        foreach (XmlNode node in cellNodes)
                                        {
                                            if (String.Compare(node.Attributes["r"].Value, addressName) > 0)
                                            {
                                                biggerNode = node;
                                                break;
                                            }
                                        }
                                    }
                                    if (biggerNode == null)
                                    {
                                        rowNode.AppendChild(cellElement);
                                    }
                                    else
                                    {
                                        rowNode.InsertBefore(cellElement, biggerNode);
                                    }
                                    returnValue = true;
                                }
                            }
                            else
                            {
                                //  You found XML corresponding to the cell location.
                                //  Remove the type (t) attribute: If the 
                                //  cell originally contained a string, you must
                                //  indicate that it no longer contains the string.
                                //  Cells containing numbers don't require the "t" attribute:
                                cellnode.Attributes.RemoveNamedItem("t");
                                XmlNode valueNode = cellnode.SelectSingleNode("d:v", nsManager);
                                if (valueNode == null)
                                {
                                    //  Cell with deleted value. Add a value element now.
                                    valueNode = xDoc.CreateElement("v", worksheetSchema);
                                    cellnode.AppendChild(valueNode);
                                }
                                valueNode.InnerText = value.ToString();
                                returnValue = true;
                            }

                            //  Save the XML back to its part.
                            xDoc.Save(sheetPart.GetStream(FileMode.Create, FileAccess.Write));
                        }
                    }
                }
                return returnValue;
            }

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="sheetName"></param>
        /// <param name="addressName"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static bool XLInsertStringIntoCell(string fileName, string sheetName, string addressName, string value)
        {
            //  Given a file, a sheet, and a cell, insert a specified string.
            //  For example: InsertStringIntoCell("C:\Test.xlsx", "Sheet3", "C3", "Microsoft")

            //  If the string exists in the shared string table, simply get its index.
            //  If the string doesn't exist in the shared string table, add it and get the next index.

            //  Then, the remainder is the same as inserting a number, but insert the string index instead
            //  of a value. Also, set the cell's t attribute to be the value "s".

            //  If the cell element (<c>) already exists, simply replace the <c> element with the new number value, maintaining formatting.
            //  If the row exists, but not the cell, insert the new <c> element.
            //  If the row doesn't exist, create the <row> element, and then insert the appropriate <c> element.
            //  In each case, you insert a fully created <c> element -- the question is how much 
            //  "stuff" you have to create around the new <c> element.

            //  Set the value of the specified cell.
            const string documentRelationshipType = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument";
            const string worksheetSchema = "http://schemas.openxmlformats.org/spreadsheetml/2006/5/main";
            const string sharedStringsRelationshipType = "http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings";
            const string sharedStringSchema = "http://schemas.openxmlformats.org/spreadsheetml/2006/5/main";

            //  Retrieve the stream containing the requested
            //  worksheet's info:
            PackagePart documentPart = null;
            Uri documentUri = null;
            bool returnValue = false;

            using (Package xlPackage = Package.Open(fileName, FileMode.Open, FileAccess.ReadWrite))
            {
                //  Get the main document part (workbook.xml).
                foreach (System.IO.Packaging.PackageRelationship relationship in xlPackage.GetRelationshipsByType(documentRelationshipType))
                {
                    //  There should only be one document part in the package. 
                    documentUri = PackUriHelper.ResolvePartUri(new Uri("/", UriKind.Relative), relationship.TargetUri);
                    documentPart = xlPackage.GetPart(documentUri);
                    //  There should only be one instance, but get out no matter what.
                    break;
                }

                if (documentPart != null)
                {
                    // Load the contents of the workbook.
                    XmlDocument doc = new XmlDocument();
                    doc.Load(documentPart.GetStream());

                    //  Create a namespace manager, so you can search.
                    //  Add a prefix (d) for the default namespace.
                    NameTable nt = new NameTable();
                    XmlNamespaceManager nsManager = new XmlNamespaceManager(nt);
                    nsManager.AddNamespace("d", worksheetSchema);
                    nsManager.AddNamespace("s", sharedStringSchema);

                    string searchString = string.Format("//d:sheet[@name='{0}']", sheetName);
                    XmlNode sheetNode = doc.SelectSingleNode(searchString, nsManager);
                    if (sheetNode != null)
                    {
                        //  Get the relId attribute:
                        XmlAttribute relationAttribute = sheetNode.Attributes["r:id"];
                        if (relationAttribute != null)
                        {
                            string relId = relationAttribute.Value;

                            //  First, get the relation between the document and the sheet.
                            PackageRelationship sheetRelation = documentPart.GetRelationship(relId);
                            Uri sheetUri = PackUriHelper.ResolvePartUri(documentUri, sheetRelation.TargetUri);
                            PackagePart sheetPart = xlPackage.GetPart(sheetUri);

                            //  Load the contents of the workbook.
                            XmlDocument xDoc = new XmlDocument(nt);
                            xDoc.Load(sheetPart.GetStream());

                            //  Set up the string information, so you can find and/or insert the string you need.
                            foreach (System.IO.Packaging.PackageRelationship stringRelationship in documentPart.GetRelationshipsByType(sharedStringsRelationshipType))
                            {
                                //  There should only be one shared string reference.
                                Uri sharedStringsUri = PackUriHelper.ResolvePartUri(documentUri, stringRelationship.TargetUri);
                                PackagePart stringPart = xlPackage.GetPart(sharedStringsUri);
                                if (stringPart != null)
                                {
                                    //  Load the contents of the shared strings.
                                    XmlDocument stringDoc = new XmlDocument(nt);
                                    stringDoc.Load(stringPart.GetStream());

                                    //  Assume the string won't be found (assign it an impossible index):
                                    int index = -1;

                                    //  Check to see if the string already exists. If so, retrieve its index.
                                    //  This search is case-sensitive, but Excel stores differently cased
                                    //  strings separately within the string file.
                                    XmlNode stringNode = stringDoc.SelectSingleNode(string.Format("//s:si[s:t='{0}']", value), nsManager);
                                    if (stringNode == null)
                                    {
                                        //  You didn't find the string in the table, so add it now.
                                        stringNode = stringDoc.CreateElement("si", sharedStringSchema);
                                        XmlElement textNode = stringDoc.CreateElement("t", sharedStringSchema);
                                        textNode.InnerText = value;
                                        stringNode.AppendChild(textNode);
                                        stringDoc.DocumentElement.AppendChild(stringNode);
                                    }

                                    if (stringNode != null)
                                    {
                                        //  You've found the node, or you've just added it.
                                        //  It had better not be a null reference. But it never hurts to check.

                                        //  Retrieve the index of the selected node.
                                        //  To do that, count the number of preceding
                                        //  nodes by retrieving a reference to those nodes.
                                        XmlNodeList nodes = stringNode.SelectNodes("preceding-sibling::d:si", nsManager);
                                        index = nodes.Count;

                                        //  Use regular expressions to get the row number.
                                        //  If the parameter wasn't well formed, this code
                                        //  will fail:
                                        System.Text.RegularExpressions.Regex r = new System.Text.RegularExpressions.Regex(@"^(?<col>\D+)(?<row>\d+)");
                                        string rowNumber = r.Match(addressName).Result("${row}");

                                        //  Search for the existing cell:
                                        XmlNode cellNode = xDoc.SelectSingleNode(string.Format("//d:sheetData/d:row/d:c[@r='{0}']", addressName), nsManager);
                                        if (cellNode == null)
                                        {
                                            //  Didn't find the cell. Look for the row. You're going to need to create
                                            //  the cell whether you find the row or not, so create that XML element now:
                                            XmlElement cellElement = xDoc.CreateElement("c", worksheetSchema);
                                            cellElement.Attributes.Append(xDoc.CreateAttribute("r"));
                                            cellElement.Attributes["r"].Value = addressName;

                                            //  Indicate that this cell will contain a string.
                                            cellElement.Attributes.Append(xDoc.CreateAttribute("t"));
                                            cellElement.Attributes["t"].Value = "s";

                                            //  Default style is "0"
                                            cellElement.Attributes.Append(xDoc.CreateAttribute("s"));
                                            cellElement.Attributes["s"].Value = "0";

                                            //  Set the cell's value:
                                            XmlElement valueElement = xDoc.CreateElement("v", worksheetSchema);
                                            valueElement.InnerText = index.ToString();
                                            cellElement.AppendChild(valueElement);

                                            //  Look for the row:
                                            XmlNode rowNode = xDoc.SelectSingleNode(string.Format("//d:sheetData/d:row[@r='{0}']", rowNumber), nsManager);
                                            if (rowNode == null)
                                            {
                                                //  Didn't find the row, either. Just add a new row element:
                                                XmlNode sheetDataNode = xDoc.SelectSingleNode("//d:sheetData", nsManager);
                                                if (sheetDataNode != null)
                                                {
                                                    XmlElement rowElement = xDoc.CreateElement("row", worksheetSchema);
                                                    rowElement.Attributes.Append(xDoc.CreateAttribute("r"));
                                                    rowElement.Attributes["r"].Value = rowNumber;
                                                    rowElement.AppendChild(cellElement);
                                                    sheetDataNode.AppendChild(rowElement);
                                                    returnValue = true;
                                                }
                                            }
                                            else
                                            {
                                                //  The row exists, but the cell does not. Add the cell information now.
                                                //  Get the style information from the parent row, if it exists.
                                                XmlAttribute styleAttr = ((XmlAttribute)(rowNode.Attributes.GetNamedItem("s")));
                                                if (styleAttr != null)
                                                {
                                                    //  You know cellElement has an "s" attribute -- you
                                                    //  added it yourself.
                                                    cellElement.Attributes["s"].Value = styleAttr.Value;
                                                }
                                                // You must insert the new cell at the correct location.
                                                // Loop through the children, looking for the first cell that is
                                                // beyond the cell you're trying to insert. Insert before that cell.
                                                XmlNode biggerNode = null;
                                                XmlNodeList cellNodes = rowNode.SelectNodes("./d:c", nsManager);
                                                if (cellNodes != null)
                                                {
                                                    foreach (XmlNode node in cellNodes)
                                                    {
                                                        if (String.Compare(node.Attributes["r"].Value, addressName) > 0)
                                                        {
                                                            biggerNode = node;
                                                            break;
                                                        }
                                                    }
                                                }
                                                if (biggerNode == null)
                                                {
                                                    rowNode.AppendChild(cellElement);
                                                }
                                                else
                                                {
                                                    rowNode.InsertBefore(cellElement, biggerNode);
                                                }
                                                returnValue = true;
                                            }
                                        }
                                        else
                                        {
                                            //  You found XML corresponding to the cell location.
                                            //  If there's a "t" attribute, make sure its value is "s".
                                            //  If not, add the "t" attribute with the value "s".
                                            if (cellNode.Attributes["t"] == null)
                                            {
                                                cellNode.Attributes.Append(xDoc.CreateAttribute("t"));
                                            }
                                            cellNode.Attributes["t"].Value = "s";
                                            XmlNode valueNode = cellNode.SelectSingleNode("d:v", nsManager);
                                            if (valueNode == null)
                                            {
                                                //  Cell with deleted value. Add a value element now.
                                                valueNode = xDoc.CreateElement("v", worksheetSchema);
                                                cellNode.AppendChild(valueNode);
                                            }
                                            valueNode.InnerText = index.ToString();
                                            returnValue = true;
                                        }

                                        //  Save the string file back to its part.
                                        stringDoc.Save(stringPart.GetStream(FileMode.Create, FileAccess.Write));

                                        //  Save the XML back to its part.
                                        xDoc.Save(sheetPart.GetStream(FileMode.Create, FileAccess.Write));
                                    }
                                }
                                //  Once you've found the match, get out:
                                break;
                            }
                        }
                    }
                }
            }
            return returnValue;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="strNumber"></param>
        /// <returns></returns>
        private static bool IsInteger(string strNumber)
        {
            System.Text.RegularExpressions.Regex objNotIntPattern = new System.Text.RegularExpressions.Regex("[^0-9-]");
            System.Text.RegularExpressions.Regex objIntPattern = new System.Text.RegularExpressions.Regex("^-[0-9]+$|^[0-9]+$");
            return !objNotIntPattern.IsMatch(strNumber) && objIntPattern.IsMatch(strNumber);
        }
    }
}
